package sqlserver;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class TestSQLServer {
	
	// 创建连接
	public Connection getConnection() {
		Connection conn = null;
		try {
			String url = "jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=demo";
			Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
			conn = DriverManager.getConnection(url, "sa", "sa");
		} catch (Exception e) {
			e.printStackTrace();
		}
		return conn;
	}
	
	// 执行SQL查询
	public void list() {
		try {
			Connection conn = getConnection();
			Statement stm = conn.createStatement();
			String sql = "select * from [user]";
			ResultSet rs = stm.executeQuery(sql);
			while (rs.next()) {
				String id = rs.getString("id");
				String username = rs.getString("username");
				String password = rs.getString("password");
				String email = rs.getString("email");
				System.out.println(id + "\t" + username + "\t" + password
						+ "\t" + email);
			}
			rs.close();
			stm.close();
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	// 执行单条查询
	public void select() {
		try {
			Connection conn = getConnection();
			Statement stm = conn.createStatement();
			String sql = "select * from [user] where id=1";
			ResultSet rs = stm.executeQuery(sql);
			if (rs.next()) {
				String id = rs.getString("id");
				String username = rs.getString("username");
				String password = rs.getString("password");
				String email = rs.getString("email");
				System.out.println(id + "\t" + username + "\t" + password
						+ "\t" + email);
			}
			rs.close();
			stm.close();
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	// 执行insert
	public void insert() {
		try {
			Connection conn = getConnection();
			Statement stm = conn.createStatement();
			String sql = "insert into [user](username, password, email) values('test','123','test@163.com')";
			stm.executeUpdate(sql);
			stm.close();
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	// 执行update
	public void update() {
		try {
			Connection conn = getConnection();
			Statement stm = conn.createStatement();
			String sql = "update [user] set username='newtest', password='new123', email='newtest@163.com' where id=8";
			stm.executeUpdate(sql);
			stm.close();
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	// 执行delete
	public void delete() {
		try {
			Connection conn = getConnection();
			Statement stm = conn.createStatement();
			String sql = "delete from [user] where id=8";
			stm.executeUpdate(sql);
			stm.close();
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public static void main(String[] args) {
		TestSQLServer test = new TestSQLServer();
		//test.insert();
		//test.update();
		test.list();
		//test.select();
	}

}
